import os
import pandas as pd
import re

def analyze_excel_and_generate_sql():
    # 遍历当前目录，寻找 Excel 文件
    current_dir = os.getcwd()
    excel_files = [f for f in os.listdir(current_dir) if f.endswith(('.xlsx', '.xls'))]

    if not excel_files:
        print("当前目录没有找到 Excel 文件。")
        return

    for excel_file in excel_files:
        print(f"正在分析文件: {excel_file}")
        # 读取 Excel 文件的第一个工作表
        df = pd.read_excel(excel_file, sheet_name=0)
        table_name = re.sub(r'\W+', '_', os.path.splitext(excel_file)[0])  # 生成表名
        generate_sql(df, table_name)

def generate_sql(df, table_name):
    # 生成创建表的 SQL 语句
    create_table_sql = f"CREATE TABLE `{table_name}` (\n"
    column_definitions = []

    # 遍历 DataFrame 的列，生成列定义
    for col in df.columns:
        column_name = re.sub(r'\W+', '_', str(col))  # 清理列名
        col_type = get_sql_type(df[col])
        column_definitions.append(f"  `{column_name}` {col_type}")

    create_table_sql += ",\n".join(column_definitions) + "\n);"

    print("\n生成的创建表语句：")
    print(create_table_sql)

    # 生成插入数据的 SQL 语句
    column_names = [f"`{re.sub(r'\\W+', '_', str(col))}`" for col in df.columns]
    insert_sql = f"INSERT INTO `{table_name}` ({', '.join(column_names)}) VALUES\n"
    values = []

    for _, row in df.iterrows():
        row_values = []
        for val in row:
            if pd.isnull(val):
                row_values.append("NULL")
            elif isinstance(val, str):
                row_values.append(f"'{val.replace('\'', '\\\'')}'")
            else:
                row_values.append(str(val))
        values.append(f"({', '.join(row_values)})")

    insert_sql += ",\n".join(values) + ";"

    print("\n生成的插入数据语句：")
    print(insert_sql)

def get_sql_type(series):
    """根据 pandas Series 的类型推测 MySQL 数据类型"""
    if pd.api.types.is_integer_dtype(series):
        return "INT"
    elif pd.api.types.is_float_dtype(series):
        return "FLOAT"
    elif pd.api.types.is_bool_dtype(series):
        return "BOOLEAN"
    elif pd.api.types.is_datetime64_any_dtype(series):
        return "DATETIME"
    else:
        return "VARCHAR(255)"  # 默认使用字符串类型

if __name__ == "__main__":
    analyze_excel_and_generate_sql()
